from flask import Flask, request, jsonify, render_template
from flask_cors import CORS
import pymysql
from pymysql.cursors import DictCursor

app = Flask(__name__)
CORS(app)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'gujinqian',
    'db': 'test',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}

def get_db():
    return pymysql.connect(**DB_CONFIG)

# 渲染主页
@app.route('/')
def index():
    return render_template('index.html')

# API: 获取学生列表
@app.route('/api/students/list', methods=['GET'])
def get_students():
    try:
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        search = request.args.get('search', '')
        offset = (page - 1) * page_size
        
        db = get_db()
        with db.cursor() as cursor:
            # 构建查询条件
            where_clause = ""
            params = []
            if search:
                where_clause = "WHERE name LIKE %s OR student_number LIKE %s OR class_name LIKE %s"
                search_param = f'%{search}%'
                params = [search_param, search_param, search_param]

            # 获取总记录数
            count_sql = f"SELECT COUNT(*) as total FROM student_info {where_clause}"
            cursor.execute(count_sql, params)
            total = cursor.fetchone()['total']

            # 获取分页数据
            data_sql = f"""
                SELECT 
                    id,
                    student_number,
                    name,
                    CAST(age AS SIGNED) as age,
                    class_name,
                    gender,
                    hometown
                FROM student_info {where_clause}
                ORDER BY id ASC
                LIMIT %s, %s
            """
            cursor.execute(data_sql, params + [offset, page_size])
            students = cursor.fetchall()
            
            # 确保数据格式正确
            formatted_students = []
            for student in students:
                formatted_students.append({
                    'id': int(student['id']),
                    'student_number': str(student['student_number']),
                    'name': str(student['name']),
                    'age': int(student['age']) if student['age'] else 0,
                    'class_name': str(student['class_name']),
                    'gender': str(student['gender']),
                    'hometown': str(student['hometown'])
                })

            return jsonify({
                'code': 0,
                'message': 'success',
                'data': {
                    'total': total,
                    'items': formatted_students
                }
            })
    except Exception as e:
        print(f"Error: {str(e)}")  # 打印错误信息
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500
    finally:
        db.close()

# API: 添加学生
@app.route('/api/students/add', methods=['POST'])
def add_student():
    try:
        data = request.json
        db = get_db()
        with db.cursor() as cursor:
            sql = """
                INSERT INTO student_info 
                (student_number, name, age, class_name, gender, hometown)
                VALUES (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (
                data['student_number'],
                data['name'],
                data['age'],
                data['class_name'],
                data['gender'],
                data['hometown']
            ))
        db.commit()
        return jsonify({
            'code': 0,
            'message': '添加成功'
        })
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': f'添加失败：{str(e)}'
        }), 500
    finally:
        db.close()

# API: 更新学生信息
@app.route('/api/students/update/<int:id>', methods=['PUT'])
def update_student(id):
    try:
        data = request.json
        db = get_db()
        with db.cursor() as cursor:
            sql = """
                UPDATE student_info 
                SET student_number=%s, name=%s, age=%s, 
                    class_name=%s, gender=%s, hometown=%s
                WHERE id=%s
            """
            cursor.execute(sql, (
                data['student_number'],
                data['name'],
                data['age'],
                data['class_name'],
                data['gender'],
                data['hometown'],
                id
            ))
        db.commit()
        return jsonify({
            'code': 0,
            'message': '更新成功'
        })
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': f'更新失败：{str(e)}'
        }), 500
    finally:
        db.close()

# API: 删除学生
@app.route('/api/students/delete/<int:id>', methods=['DELETE'])
def delete_student(id):
    try:
        db = get_db()
        with db.cursor() as cursor:
            cursor.execute("DELETE FROM student_info WHERE id=%s", (id,))
        db.commit()
        return jsonify({
            'code': 0,
            'message': '删除成功'
        })
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': f'删除失败：{str(e)}'
        }), 500
    finally:
        db.close()

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)
